#! /usr/bin/env python  
# -*- coding: utf-8 -*-  
# __author__ = "shuke"  
# Date: 2018/5/13  
import hashlib   
import pymysql  
from dbutils.pooled_db import PooledDB
import sys,os
sys.path.append(os.getcwd())
from src.utils.tool import Tool


# 下载文件
# 创建数据库连接池
tempPool = PooledDB(
    creator=pymysql,  # 使用链接数据库的模块
    maxconnections=6,  # 连接池允许的最大连接数
    mincached=2,  # 初始化时，链接池中至少创建的空闲的链接
    maxcached=5,  # 链接池中最多闲置的链接
    maxshared=3,  # 链接池中最多共享的链接数量
    blocking=True,  # 连接池中如果没有可用连接后，是否阻塞等待
    host='192.168.10.191',
    port=3306,
    user='writer',
    password='zlc1002020',
    database='wisdom-exam-db',
    charset='utf8mb4'  # 设置编码
)

# 获取数据
def getScriptQuestions():
    conn = tempPool.connection()
    cursor = conn.cursor()

    # 执行SQL语句
    sql = "SELECT id,topic_key,choicesList,stem,analysis,answer FROM script_questions WHERE is_download = 0 ORDER BY id ASC limit 100"
    cursor.execute(sql)

    # 获取查询结果
    result = cursor.fetchall()

    # 不关闭连接，而是将连接放回连接池
    cursor.close()
    conn.close()
    
    return result

def saveFiles(row,img):
    print(img)
    conn = tempPool.connection()
    cursor = conn.cursor()
    #插入单行数据
    name = Tool.get_file_name(img)
    path = img
    topic_key_hash = Tool.md5_32(str(row[1]))
    hash = Tool.md5_32(img)
    sql = "INSERT INTO script_files (name, path,topic_key_hash,hash) VALUES (%s, %s, %s, %s)"
    cursor.execute(sql, (name, path,topic_key_hash,hash))
    # 提交更改到数据库
    conn.commit()   
    # 不关闭连接，而是将连接放回连接池
    cursor.close()
    conn.close() 

# 更新标识为下载    
def updateDownload(id):
    conn = tempPool.connection()
    cursor = conn.cursor()
    # 编写更新语句
    update_sql = "UPDATE script_questions SET is_download = 1 WHERE id="+str(id)
    # 执行更新语句
    cursor.execute(update_sql)
    # 提交事务
    conn.commit()
    # 关闭游标和数据库连接
    cursor.close()
    conn.close()

def run():
  i = 1
  while i <= 50000:
    # 获取试题
    
    lists = getScriptQuestions()      
    print(lists)
    # id,topic_key,choicesList,stem,analysis,answer
    for row in lists:

        print(row)    
        choicesImgs = Tool.extract_image_urls2(str(row[2]))
        for choicesImg in choicesImgs:
            saveFiles(row,choicesImg)

        stemImgs = Tool.extract_image_urls2(str(row[3]))
        for stemImg in stemImgs:
            saveFiles(row,stemImg)

        analysisImgs = Tool.extract_image_urls2(str(row[4]))
        for analysisImg in analysisImgs:
            saveFiles(row,analysisImg)

        answerImgs = Tool.extract_image_urls2(str(row[5]))
        for answerImg in answerImgs:
            saveFiles(row,answerImg)

        # 更新   
        updateDownload(row[0])  

run()



    
    
    